import pymysql
from surprise import SVD, Reader, Dataset
from surprise.model_selection import GridSearchCV
from collections import defaultdict
from surprise.dump import dump,load

import pandas as pd


class GetDataDb:
    def __init__(self):
        print('************正在连接数据库')
        self.conn = pymysql.connect(
            host='localhost',
            port=3306,
            db='movie',
            user='root',
            password='111111',
            charset='utf8'
        )
        self.cursor = self.conn.cursor()

    def getData(self):
        print('***************正在提取数据')
        sql = 'select user_id,movie_id,behavior_score from behavior'
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        # self.conn.commit()
        print(data)
        return data

    def close_db(self):
        print('*********正在关闭mysql')
        self.cursor.close()
        self.conn.close()


# def convert_toData(data):
#     user_recommand = {}
#     for row in data:
#         user_id,movie_id,rating = row
#         if user_id not in user_recommand:
#             user_recommand[user_id] = []
#         user_recommand[user_id].append((movie_id,rating))
#     return user_recommand
#
# 将数据库数据转换成DataFrame
class Recommand_UserCf():
    def __init__(self, data):
        self.data = data
        self.path = './model.plf'

    def trans_to_pd(self):
        new_data = pd.DataFrame(self.data, columns=[['user_id', 'movie_id', 'rating']])
        # train_data,test_data = train_test_split(new_data,test_size=0.1,random_state=0)
        return new_data

    # 根据提供的reader对象将数据转换为适用于推荐系统的格式
    def trans_to_SVD(self):
        data = self.trans_to_pd()
        ratings_reader = Reader(rating_scale=(1, 10))
        train_Data = Dataset.load_from_df(data, reader=ratings_reader)
        return train_Data

    def build_model(self):
        train_data = self.trans_to_SVD()
        param_grid = {'n_epochs': [10], 'lr_all': [0.02, 0.05], 'reg_all': [0.15, 0.2]}
        gs = GridSearchCV(SVD, param_grid, measures=['rmse', 'mae'], cv=2)
        gs.fit(train_data)
        # 获取通过网格搜索得到的最佳模型，通过索引'rmse'获取对应的SVD模型
        svd_model = gs.best_estimator['rmse']
        # 使用build_full_trainset方法从训练数据集中构建一个适用于SVD模型的完整训练集
        fit_data = train_data.build_full_trainset()
        # 使用构建的完整训练集对SVD模型进行训练，即拟合模型
        svd_model.fit(trainset=fit_data)
        return svd_model

    def save_model(self, model, save_path):
        model = self.build_model()
        try:
            dump(self.path, algo=model)
        except Exception as e:
            print('保存失败，再试一次')

    def load_model(self):
        try:
            model = load(self.path)
        except Exception as e:
            return None
        return model

    def predict_all(self):
        model = self.build_model()
        train_data = self.trans_to_SVD()
        # 从训练数据集中构建一个适用于模型的完整训练集
        fit_data = train_data.build_full_trainset()
        # 构建一个测试集，其中包含了所有用户对所有未评分项的评分预测
        predicting_set = fit_data.build_anti_testset()
        # 使用已训练好的模型model对测试集predicting_set进行评分预测，得到一个评分预测列表
        prediction_list = model.test(predicting_set)

        return prediction_list

    def build_user_prediction(self):
        prediction_list = self.predict_all()
        prediction_dict = defaultdict(list)
        for p in prediction_list:
            prediction_dict[str(p.uid)].append((p.iid, p.est))
        sorted_prediction_dict = {}
        # 遍历其中的每个用户和对应的电影评分列表
        for user_id, item_ratings in prediction_dict.items():
            # 对电影评分列表进行排序，按照评分（x[1]）进行降序排序
            item_ratings.sort(key=lambda x: x[1], reverse=True)
            sorted_prediction_dict[user_id] = item_ratings
        return sorted_prediction_dict

    def tok_k_recommand(self, user_id, k, times):
        predicted_ratings = self.build_user_prediction()
        position = times * k + 1
        if predicted_ratings:
            movie_list = predicted_ratings[user_id][position:position + k]
        return movie_list


if __name__ == '__main__':
    init = GetDataDb()
    data = init.getData()
    Recommand = Recommand_UserCf(data)
    top_k = Recommand.tok_k_recommand('14', 6, 1)
    # print(top_k)
    print(top_k)
    init.close_db()
